<?php

/** PHPExcel */
require_once '../Classes/PHPExcel.php';
include_once '../dataaccess/cs_parameter.php';
//include '../../php/dbconfig.php';	
class ExportExcel {
    /*
      function  ExportExcel($localhostMysql,$userNameMysql,$passWordMysql,$dbNameMysql)
      {
      global $localhost,$userName,$passWord,$dbName;
      $localhost=$localhostMysql;
      $userName=$userNameMysql;
      $passWord=$passWordMysql;
      $dbName=$dbNameMysql;
      }
     */

    function ExportExcel($exportType="tmp_event") {
        $this->OpenConnection();
        //mysql_query("set character set 'utf8'");//读库
        //mysql_query("set names 'utf8'");//写库
        global $document_root, $server_name, $server_address, $path, $export_type;
        $document_root = $_SERVER["DOCUMENT_ROOT"];
        $server_name = $_SERVER["SERVER_NAME"];
        $server_address = $_SERVER["SERVER_ADDR"];
        $path = $this->getDefaultDownloadPathString();
        $export_type = $exportType; //tmp_event,tmp_event_type,tmp_venue
    }

//打开数据库连接$localhost,$userName,$passWord,$dbName
    function OpenConnection() {
        /*
          global $localhost,$userName,$passWord,$dbName;
          //change to your database server/user name/password
          mysql_connect($localhost,$userName,$passWord) or
          die("Could not connect: " . mysql_error());
          //change to your database name
          mysql_select_db($dbName) or
          die("Could not select database: " . mysql_error());
         */
        $db = new DBConnection();
        $db->getConnection();
    }

    //取得不合格的记录数
    function getHasErrorRecordNum() {
        global $export_type;
        $strSql = "SELECT COUNT(1) AS totalCount FROM " . $export_type . " WHERE checkResult IS NOT NULL;"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $row = mysql_fetch_object($handle);
        $totalCount = $row->totalCount;
        return $totalCount;
    }

    //取得所有导入的记录数
    function getTotalImportRecordNum() {
        global $export_type;
        $strSql = "SELECT COUNT(1) AS totalCount FROM " . $export_type . ";"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $row = mysql_fetch_object($handle);
        $totalCount = $row->totalCount;
        return $totalCount;
    }

    //取得所有合法的记录数
    function getTotalPassRecordNum() {
        global $export_type;
        $strSql = "SELECT COUNT(1) AS totalCount FROM " . $export_type . " WHERE checkResult IS NULL;"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $row = mysql_fetch_object($handle);
        $totalCount = $row->totalCount;
        return $totalCount;
    }

    //取出所有不合格的记录，导出成Excel 2007文件,$path不包含文件名，eg:
    //$paht=/file/download/
    //$document_root:D:/PHPWeb
    //$server_name:localhost
    //$server_address:127.0.0.1
    function downloadHasErrorRecors() {
        global $document_root, $server_name, $server_address, $path,$export_type;
        $fileName = $path . 'HasError_' . date('YmdHis') . '.xlsx';
        switch ($export_type) {
            case "tmp_event":
               $this->dowloadHasErrorRecoresMaster($fileName);
                break;
            case "tmp_event_type":
                $this->dowloadHasErrorRecoresEventType($fileName);
                break;
            case "tmp_city":
                $this->dowloadHasErrorRecoresCity($fileName);
                break;
            case "tmp_venue":
                $this->dowloadHasErrorRecoresVenue($fileName);
                break;
            default:
                break;
        }
        return $server_name . $fileName;
    }

    function dowloadHasErrorRecoresMaster($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C,D,E,F,G,H FROM " . $export_type . " WHERE checkResult IS NOT NULL;"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->export2Excel2007($handle, $document_root . $fileName);
    }

    function dowloadHasErrorRecoresEventType($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C FROM " . $export_type . " WHERE checkResult IS NOT NULL;"; //tmp_event_type
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->exportEventType2Excel2007($handle, $document_root . $fileName);
    }
    function dowloadHasErrorRecoresCity($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C FROM " . $export_type . " WHERE checkResult IS NOT NULL;"; //tmp_city
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->exportEventType2Excel2007($handle, $document_root . $fileName);
    }
    function dowloadHasErrorRecoresVenue($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C,D,E,F,G,H,I,J FROM " . $export_type . " WHERE checkResult IS NOT NULL;"; //tmp_event_type
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->exportEventType2Excel2007($handle, $document_root . $fileName);
    }
    //取出所有合格的记录，导出成Excel 2007文件,$path不包含文件名，eg:
    //$paht=/file/download/
    //$document_root:D:/PHPWeb
    //$server_name:localhost
    //$server_address:127.0.0.1
    function downloadPassRecors() {
        global $export_type;
        global $document_root, $server_name, $server_address, $path;
        $fileName = $path . 'Pass_' . date('YmdHis') . '.xlsx';
        switch ($export_type) {
            case "tmp_event":
                $this->downloadPassRecordsMaster($fileName);
                break;
            case "tmp_event_type":
                $this->downloadPassRecordsEventType($fileName);
                break;
            case "tmp_city":
                $this->downloadPassRecordsCity($fileName);
                break;
            case "tmp_venue":
                $this->downloadPassRecordsVenue($fileName);
                break;
            default:
                break;
        }
        return $server_name . $fileName;
    }

    function downloadPassRecordsMaster($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C,D,E,F,G,H FROM " . $export_type . " WHERE checkResult IS NULL;"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->export2Excel2007($handle, $document_root . $fileName);
    }

    function downloadPassRecordsEventType($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C FROM " . $export_type . " WHERE checkResult IS NULL;"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->exportEventType2Excel2007($handle, $document_root . $fileName);
    }
    function downloadPassRecordsCity($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C FROM " . $export_type . " WHERE checkResult IS NULL;"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->exportEventType2Excel2007($handle, $document_root . $fileName);
    }
    function downloadPassRecordsVenue($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C,D,E,F,G,H,I,J FROM " . $export_type . " WHERE checkResult IS NULL;"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->exportEventType2Excel2007($handle, $document_root . $fileName);
    }
    //取出所有记录，导出成Excel 2007文件,$path不包含文件名，eg:
    //$paht=/file/download/
    //$document_root:D:/PHPWeb
    //$server_name:localhost
    //$server_address:127.0.0.1
    function downloadAllRecors() {
        global $export_type;
        global $document_root, $server_name, $server_address, $path;
        $fileName = $path . 'All_' . date('YmdHis') . '.xlsx';
        switch ($export_type) {
            case "tmp_event":
                $this->downloadAllRecorsMaster($fileName);
                break;
            case "tmp_event_type":
                $this->downloadAllRecorsEventType($fileName);
                break;
            case "tmp_city":
                $this->downloadAllRecorsCity($fileName);
                break;
            case "tmp_venue":
                $this->downloadAllRecorsVenue($fileName);
                break;
            default:
                break;
        }
        return $server_name . $fileName;
    }

    function downloadAllRecorsMaster($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C,D,E,F,G,H FROM " . $export_type . ";"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->export2Excel2007($handle, $document_root . $fileName);
    }

    function downloadAllRecorsEventType($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C FROM " . $export_type . ";"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->exportEventType2Excel2007($handle, $document_root . $fileName);
    }
     function downloadAllRecorsCity($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C FROM " . $export_type . ";"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->exportEventType2Excel2007($handle, $document_root . $fileName);
    }
    function downloadAllRecorsVenue($fileName) {
        global $export_type,$document_root;
        $strSql = "SELECT A,B,C,D,E,F,G,H,I,J FROM " . $export_type . ";"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        $this->exportEventType2Excel2007($handle, $document_root . $fileName);
    }

    //导出成Excel
    function export2Excel2007($handle, $fileName) {
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("amcnn")
                ->setLastModifiedBy("amcnn")
                ->setTitle("Office 2007 XLSX Master")
                ->setSubject("Office 2007 XLSX Master")
                ->setDescription("The document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("master");
        //设置行头							 
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'Festival')
                ->setCellValue('B1', 'Subject')
                ->setCellValue('C1', 'Event_Type')
                ->setCellValue('D1', 'City')
                ->setCellValue('E1', 'Venue')
                ->setCellValue('F1', 'Start date')
                ->setCellValue('G1', 'End Date')
                ->setCellValue('H1', 'Time');

        $crow = 2;
        while (($row = mysql_fetch_object($handle)) != null) {
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . $crow, $row->A)
                    ->setCellValue('B' . $crow, $row->B)
                    ->setCellValue('C' . $crow, $row->C)
                    ->setCellValue('D' . $crow, $row->D)
                    ->setCellValue('E' . $crow, $row->E)
                    ->setCellValue('F' . $crow, $row->F)
                    ->setCellValue('G' . $crow, $row->G)
                    ->setCellValue('H' . $crow, $row->H);
            $crow++;
        }

        $objPHPExcel->getActiveSheet()->setTitle('master');
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
        $objWriter->save($fileName);
    }

    //导出成Excel
    function exportEventType2Excel2007($handle, $fileName) {
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("amcnn")
                ->setLastModifiedBy("amcnn")
                ->setTitle("Office 2007 XLSX Event Type")
                ->setSubject("Office 2007 XLSX Event Type")
                ->setDescription("The document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("event_type");
        //设置行头							 
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'Name_en_us')
                ->setCellValue('B1', 'Name_zh_hk')
                ->setCellValue('C1', 'Name_zh_cn');

        $crow = 2;
        while (($row = mysql_fetch_object($handle)) != null) {
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . $crow, $row->A)
                    ->setCellValue('B' . $crow, $row->B)
                    ->setCellValue('C' . $crow, $row->C);
            $crow++;
        }

        $objPHPExcel->getActiveSheet()->setTitle('event_type');
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
        $objWriter->save($fileName);
    }

    function exportVenue2Excel2007($handle, $fileName) {
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("amcnn")
                ->setLastModifiedBy("amcnn")
                ->setTitle("Office 2007 XLSX Venue")
                ->setSubject("Office 2007 XLSX Venue")
                ->setDescription("The document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("venue");
        //设置行头							 
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'Name_en_us')
                ->setCellValue('B1', 'Name_zh_hk')
                ->setCellValue('C1', 'Name_zh_cn')
                ->setCellValue('D1', 'Telephone')
                ->setCellValue('E1', 'Address_en_us')
                ->setCellValue('F1', 'Address_zh_hk')
                ->setCellValue('G1', 'Address_zh_cn')
                ->setCellValue('H1', 'City_name_zh_hk')
                ->setCellValue('I1', 'URLs')
                ->setCellValue('J1', 'Contact_Person');

        $crow = 2;
        while (($row = mysql_fetch_object($handle)) != null) {
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . $crow, $row->A)
                    ->setCellValue('B' . $crow, $row->B)
                    ->setCellValue('C' . $crow, $row->C)
                    ->setCellValue('D' . $crow, $row->D)
                    ->setCellValue('E' . $crow, $row->E)
                    ->setCellValue('F' . $crow, $row->F)
                    ->setCellValue('G' . $crow, $row->G)
                    ->setCellValue('H' . $crow, $row->H)
                    ->setCellValue('I' . $crow, $row->I)
                    ->setCellValue('J' . $crow, $row->J);
            $crow++;
        }

        $objPHPExcel->getActiveSheet()->setTitle('venue');
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
        $objWriter->save($fileName);
    }

    //应用已经Pass的数据，从tmp_event->event
    function applyPassRecords() {

        global $export_type;
        switch ($export_type) {
            case "tmp_event":
                $strDefaultTime = $this->getDefaultTimeString();
                $strSql = "INSERT INTO event (festival,`subject`,etid,vid,start_time,end_time,is_allday_event,delete_flag,`uid`) 
		SELECT A,B,existEtid,existVid,
		CASE WHEN H='' THEN CONCAT(F,' 00:00:00') ELSE CONCAT(F,' ',H) END AS startTime,
		CASE WHEN H='' THEN CONCAT(G,' " . $strDefaultTime . "') ELSE CONCAT(F,' " . $strDefaultTime . "') END AS endTime,
		CASE WHEN H='' THEN 1 ELSE 0 END AS is_allday_event,0,3
		 FROM tmp_event
		 WHERE checkResult IS NULL;";
                break;
            case "tmp_event_type":
                $strSql = "INSERT INTO event_type(name_en_us,name_zh_hk,name_zh_cn,delete_flag) 
                        SELECT A,B,C,0 FROM tmp_event_type
                        WHERE checkResult IS NULL;";
                break;
            case "tmp_city":
                $strSql = "INSERT INTO city(name_en_us,name_zh_hk,name_zh_cn,delete_flag) 
                        SELECT A,B,C,0 FROM tmp_city
                        WHERE checkResult IS NULL;";
                break;
            case "tmp_venue":
                $strSql = "INSERT INTO venue (name_en_us,name_zh_hk,name_zh_cn,telephones,address_en_us,address_zh_hk,address_zh_cn,delete_flag,cid,urls,contact_person)
                        SELECT A,B,C,D,E,F,G,0,existCid,I,J FROM tmp_venue
                        WHERE checkResult IS NULL;";
                break;
            default:
                break;
        }

        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        if ($handle == false) {
            return mysql_error();
        } else {
            return $handle; //true
        }
    }

    //删除已经Pass的数据，从tmp_event/tmp_event_type/tmp_venue
    function deleteApplyPassRecords() {
        global $export_type;
        $strSql = "DELETE FROM `" . $export_type . "` WHERE checkResult IS NULL;"; //tmp_event
        //$this->OpenConnection();
        $handle = mysql_query($strSql);
        if ($handle == false) {
            return mysql_error();
        } else {
            return $handle; //受影响的记录数
        }
    }

    //取得默认时间的串
    function getDefaultTimeString() {
        $cs_parameter=new cs_parameter();
        return $cs_parameter->getDefaultTime();
        //return "23:59:59"; //要从配置表里面取
    }

    function getDefaultDownloadPathString() {
        $cs_parameter=new cs_parameter();
        return $cs_parameter->getDownloadPath();
       // return "/download/"; //要从配置表里面取
    }

    //按条件导出event
    function downloadEvent($festival='', $subject='', $event_type='', $city='', $venue='', $start_time='', $end_time='', $x_days=0) {
        $handle = null;
        global $document_root, $server_name, $server_address, $path;
        $strTime = '';

        if ($x_days != 0) {
            //x_days優先於start_time
            //$today = getdate();
            $start_time = mktime(date("H"), date("i"), date("s"), date("m"), date("d") + $x_days, date("Y"));
        }

        if ($start_time != '' and $end_time == '') {
            $strTime = "AND e.start_time>='" . $start_time . "'"; //AND (e.start_time>='2011-07-19 23:59:59' OR ''='2011-07-19 23:59:59')
        }
        if ($start_time == '' and $end_time != '') {
            $strTime = "AND e.end_time<='" . $end_time . "'"; //AND (e.end_time<='2011-07-19 23:59:59' OR ''='2011-07-19 23:59:59')
        }
        if ($start_time != '' and $end_time != '') {
            //AND ((e.start_time>='2010-12-17 00:00:00' AND e.end_time<='2011-07-19 23:59:59') OR (''='2010-12-17 00:00:00' AND ''='2011-05-29 23:59:59'))
            $strTime = "AND ((e.start_time>='" . $start_time . "' AND e.end_time<='" . $end_time . "') OR (''='" . $start_time . "' AND ''='" . $end_time . "'))";
        }
        $strSql = "SELECT e.festival AS A,e.subject AS B,et.name_zh_hk AS C,c.name_zh_hk AS D,v.name_zh_hk AS E,
				CAST(e.start_time AS DATE) AS F,
				CASE WHEN e.is_allday_event=1 THEN CAST(end_time AS DATE) ELSE NULL END AS G,
				CASE WHEN e.is_allday_event=0 THEN CAST(e.start_time AS TIME) ELSE NULL END AS H 
				FROM event e
				JOIN venue v ON e.vid=v.vid
				JOIN city c ON v.cid=c.cid
				JOIN event_type et ON et.etid=e.etid
				WHERE (e.festival='" . $festival . "' OR '" . $festival . "'='')
					AND (et.name_zh_hk='" . $event_type . "' OR ''='" . $event_type . "')
					AND (c.name_zh_hk='" . $city . "' OR ''='" . $city . "')
					AND (v.name_zh_hk='" . $venue . "' OR ''='" . $venue . "') " . $strTime . "
					AND (e.subject='" . $subject . "' OR ''='" . $subject . "')";

        $fileName = $path . 'Event_' . date('YmdHis') . '.xlsx';
        //$this->OpenConnection();
        //echo "<br>".$strSql."<br>";
        //mysql_query("set character set 'utf8'");//读库
        //mysql_query("SET NAMES utf8");//使用GBK，可以查询出正确的记录数，但写excel文件时有乱码
        $handle = mysql_query($strSql);
        $this->export2Excel2007($handle, $document_root . $fileName);
        return $server_name . $fileName;
    }

}

?>